﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class SqlEmplois_matiere
    {
        private static Emplois_matiere ReaderToObject(SqlDataReader dr)
        {
            Emplois_matiere _Emplois_matiere = new Emplois_matiere();
            _Emplois_matiere.Id = int.Parse(dr["Id"].ToString());


            if (!object.ReferenceEquals(dr["Nbr_heure"], DBNull.Value))
            {
                _Emplois_matiere.Nbr_heure = int.Parse(dr["Nbr_heure"].ToString());
            }

            if (!object.ReferenceEquals(dr["Id_niveau"], DBNull.Value))
            {
                _Emplois_matiere.niveau   = Niveau.GetNiveauById(int.Parse(dr["Id_niveau"].ToString()));
            }


            if (!object.ReferenceEquals(dr["Id_specialite"], DBNull.Value))
            {
                _Emplois_matiere.specialite = Specialite.GetSpecialiteById(int.Parse(dr["Id_specialite"].ToString()));
            }

            if (!object.ReferenceEquals(dr["Id_matiere"], DBNull.Value))
            {
                _Emplois_matiere.matiere = Matiere.GetMatiereById(int.Parse(dr["Id_matiere"].ToString()));
            }
            return _Emplois_matiere;
        }

        public static Emplois_matiere GetEmplois_matiereById(int id)
        {
            Emplois_matiere emplois_matiere = new Emplois_matiere();
            string strSQL = @"select * from Emplois_matiere where UserId=@Id";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        emplois_matiere = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return emplois_matiere;
        }
    
         public static int insertEmplois_matiere(Emplois_matiere EM)
        {
            int n = 0;            
            string strSQL = @"insert into Emplois_matiere(Nbr_heure,Id_niveau,Id_specialite,Id_matiere) values(@Nbr_heure,@Id_niveau,@Id_specialite,@Id_matiere)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Nbr_heure",EM .Nbr_heure );
                    
                    cmd.Parameters.AddWithValue("@Id_niveau", EM.niveau .Id);
                     cmd.Parameters.AddWithValue("@Id_specialite", EM.specialite .Id);
                    cmd.Parameters.AddWithValue("@Id_matiere", EM.matiere .Id);
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();                    
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateEmplois_matiere( Emplois_matiere EM)
        {
             int n = 0;

             string strSQl = @"UPDATE Emplois_matiere SET (@Nbr_heure IS NULL OR Nbr_heure=@Nbr_heure) ,"+
                 "Id_niveau=@Id_niveau, Id_specialite=@Id_specialite,Id_matiere=@Id_matiere where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    
                     SqlCommand cmd = new SqlCommand(strSQl, cnx);
                    cmd.Parameters.AddWithValue("@Nbr_heure",EM .Nbr_heure );
                    
                    cmd.Parameters.AddWithValue("@Id_niveau", EM.niveau .Id);
                     cmd.Parameters.AddWithValue("@Id_specialite", EM.specialite .Id);
                    cmd.Parameters.AddWithValue("@Id_matiere", EM.matiere .Id);
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();                
                }
            }
            catch (Exception) { }
            return n;
        }
       


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deleteEmplois_matiere(int id){ 
            int n = 0;            
            string strSQL = @"delete from Emplois_matiere where id=@id)";
           
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);//req pr delete
                    cmd.Parameters.AddWithValue("@id", id);                   
                    if(cmd.ExecuteNonQuery()>0)
                    n=  cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }

    }
}
    


